package com.leexuan.ordersystem.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.leexuan.ordersystem.beans.Product;

public class ProductDB extends DB {

	private String ADD_PRODUCT = "INSERT INTO product VALUES (?,?,?,?,?,?,?)";
	private String DEL_PRODUCT = "DELETE FROM product WHERE pid=?";
	private String UPDATE_PRODUCT = "UPDATE product SET number=?,model=?,name=?,price=?,sid=?,num=? WHERE pid=?";
	private String GET_PRODUCTS = "SELECT p.*,s.name AS sname FROM product AS p INNER JOIN sort AS s ON p.sid=s.sid ORDER BY p.number ASC";
	private String GET_PRODUCT_BY_ID = "SELECT p.*,s.name AS sname FROM product AS p INNER JOIN sort AS s ON p.sid=s.sid WHERE p.pid=?";
	private String GET_PRODUCTS_BY_SID = "SELECT p.*,s.name AS sname FROM product AS p INNER JOIN sort AS s ON p.sid=s.sid WHERE p.sid=? ORDER BY p.number ASC";

	private String GET_PRODUCT_NUM = "SELECT num FROM product WHERE pid=?";
	private String SUB_PRODUCT_NUM = "UPDATE product SET num=num-? WHERE pid=?";

	public Product getProduct(int pid) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Product product = new Product();
		try {
			pstmt = conn.prepareStatement(GET_PRODUCT_BY_ID);
			pstmt.setInt(1, pid);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				product.setPid(rs.getInt("pid"));
				product.setSid(rs.getInt("sid"));
				product.setName(rs.getString("name"));
				product.setModel(rs.getString("model"));
				product.setNumber(rs.getString("number"));
				product.setPrice(rs.getFloat("price"));
				product.setNum(rs.getInt("num"));
				product.setSname(rs.getString("sname"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				conn.close();
				conn = null;
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
		return product;
	}

	public int getProductNum(int pid) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		int num = 0;

		try {
			pstmt = conn.prepareStatement(GET_PRODUCT_NUM);
			pstmt.setInt(1, pid);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				num = rs.getInt("num");

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				conn.close();
				conn = null;
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
		return num;
	}

	public boolean subProductNum(int subnum, int pid) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(SUB_PRODUCT_NUM);

			pstmt.setInt(1, subnum);
			pstmt.setInt(2, pid);

			return pstmt.execute();

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
	}

	public boolean addProduct(Product pt) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(ADD_PRODUCT);

			pstmt.setString(1, pt.getNumber());
			pstmt.setString(2, pt.getModel());
			pstmt.setString(3, pt.getName());
			pstmt.setFloat(4, pt.getPrice());
			pstmt.setInt(5, pt.getSid());
			pstmt.setInt(6, pt.getNum());
			pstmt.setString(7, "/images/noPic.jpg");

			return pstmt.execute();

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
	}

	public boolean updateProduct(Product pt, int pid) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(UPDATE_PRODUCT);

			pstmt.setString(1, pt.getNumber());
			pstmt.setString(2, pt.getModel());
			pstmt.setString(3, pt.getName());
			pstmt.setFloat(4, pt.getPrice());
			pstmt.setInt(5, pt.getSid());
			pstmt.setInt(6, pt.getNum());
			pstmt.setInt(7, pid);

			return pstmt.execute();

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
	}

	public boolean delProduct(int pid) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(DEL_PRODUCT);

			pstmt.setInt(1, pid);

			return pstmt.execute();

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
	}

	public ArrayList getProducts() throws SQLException {

		PreparedStatement pstmt = null;
		ResultSet rs = null;

		ArrayList products = new ArrayList();

		try {
			pstmt = conn.prepareStatement(GET_PRODUCTS, ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			rs = pstmt.executeQuery();

			while (rs.next()) {

				Product product = new Product();

				product.setSid(rs.getInt("sid"));
				product.setNum(rs.getInt("num"));
				product.setPid(rs.getInt("pid"));
				product.setPrice(rs.getFloat("price"));

				product.setName(rs.getString("name"));
				product.setSname(rs.getString("sname"));
				product.setModel(rs.getString("model"));
				product.setNumber(rs.getString("number"));

				products.add(product);

			}

		} catch (SQLException ex) {
			ex.printStackTrace();

			throw new SQLException("SQLException on : DAO");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}

		}
		return products;
	}

	public ArrayList getProductsBySid(int sid) throws SQLException {

		PreparedStatement pstmt = null;
		ResultSet rs = null;

		ArrayList products = new ArrayList();

		try {
			pstmt = conn.prepareStatement(GET_PRODUCTS_BY_SID, ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);

			pstmt.setInt(1, sid);
			rs = pstmt.executeQuery();

			while (rs.next()) {

				Product product = new Product();

				product.setSid(rs.getInt("sid"));
				product.setNum(rs.getInt("num"));
				product.setPid(rs.getInt("pid"));
				product.setPrice(rs.getFloat("price"));

				product.setName(rs.getString("name"));
				product.setSname(rs.getString("sname"));
				product.setModel(rs.getString("model"));
				product.setNumber(rs.getString("number"));

				products.add(product);

			}

		} catch (SQLException ex) {
			ex.printStackTrace();

			throw new SQLException("SQLException on : DAO");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}

		}
		return products;
	}

	public ArrayList findProducts(String sql) throws SQLException {

		PreparedStatement pstmt = null;
		ResultSet rs = null;

		ArrayList products = new ArrayList();

		try {
			pstmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			rs = pstmt.executeQuery();

			while (rs.next()) {

				Product product = new Product();

				product.setSid(rs.getInt("sid"));
				product.setNum(rs.getInt("num"));
				product.setPid(rs.getInt("pid"));
				product.setPrice(rs.getFloat("price"));

				product.setName(rs.getString("name"));
				product.setSname(rs.getString("sname"));
				product.setModel(rs.getString("model"));
				product.setNumber(rs.getString("number"));

				products.add(product);

			}

		} catch (SQLException ex) {
			ex.printStackTrace();

			throw new SQLException("SQLException on : DAO");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}

		}
		return products;
	}
}
